package com.clouds.wms.dao;

import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import com.clouds.wms.bean.Cmsmh;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;
import com.clouds.wms.util.PagesBean;

@Repository("cmsmhDao")
public class CmsmhDaoImpl implements CmsmhDao {
	
	private Log log = LogFactory.getLog(getClass());
	
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	
	@Override
	public PagesBean getCmsmhList(String mhaa, String mhab, int mhad,int offset, int pageSize) throws DatabaseException {
		PagesBean pb = new PagesBean();
		List<Cmsmh> list = new ArrayList<Cmsmh>();
		boolean hasWhere = false;
		StringBuffer sql = new StringBuffer("SELECT mhaa,mhab,mhac,mhad FROM cmsmh ");
		if(mhaa != null && !"".equals(mhaa)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mhaa LIKE '%" + Function.formatSQLString(mhaa) + "%'");
		}
		if(mhab != null && !"".equals(mhab)) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mhab LIKE '%" + Function.formatSQLString(mhab) + "%'");
		}
		if(mhad != 0 ) {
			hasWhere = Function.appendWhereIfNeed(sql, hasWhere);
			sql.append( " mhad = " + mhad + "");
		}
		sql.append(" ORDER BY mhaa DESC");
		try {
			int totalCount = namedParameterJdbcTemplate.getJdbcOperations().queryForInt(Function.toCountSql(sql.toString()));
			if(totalCount!=0){
				list = namedParameterJdbcTemplate.getJdbcOperations().query(Function.mySqlQuery(sql.toString(),offset,pageSize,totalCount),new BeanPropertyRowMapper<Cmsmh>(Cmsmh.class));
			}
			pb.setCount(totalCount);
			pb.setData(list);
			return pb;
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public Cmsmh getCmsmhByMhaa(String mhaa) throws DatabaseException {
		String sql = "SELECT * FROM cmsmh WHERE mhaa=? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{mhaa}, new BeanPropertyRowMapper<Cmsmh>(Cmsmh.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int saveCmsmh(Cmsmh cmsmh) throws DatabaseException {
		String sql = "INSERT INTO cmsmh (mhaa,mhab,mhac,mhad) VALUES (:mhaa,:mhab,:mhac,:mhad)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmh);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmhMham(String mhaa, int mham) throws DatabaseException {
		String sql = "UPDATE cmsmh SET mhad=? WHERE mhaa=? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql,new Object[]{mham,mhaa});
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmh(Cmsmh cmsmh) throws DatabaseException {
		String sql = "UPDATE cmsmh SET mhab=:mhab,mhac=:mhac,mhad=:mhad WHERE mhaa=:mhaa ";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmh);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmh> getAllCmsmhList(int mham) throws DatabaseException {
		String sql = "SELECT * FROM cmsmh WHERE mham=? ORDER BY mhaa DESC";
		return namedParameterJdbcTemplate.getJdbcOperations().query(sql, new Object[]{mham}, new BeanPropertyRowMapper<Cmsmh>(Cmsmh.class));
	}

	@Override
	public List<Cmsmh> getAllCmsmhList(String mhac, int mham, String ecaa, String ecad, String ecae) throws DatabaseException {
		String sql = "SELECT mhaa,mhab,mhac,mhad FROM cmsmh WHERE mham=? ";
		if(mhac != null && !"".equals(mhac)) {
			sql += " AND mhac='"+mhac+"'";
		}
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql, new Object[]{mham, ecaa, ecad, ecae}, new BeanPropertyRowMapper<Cmsmh>(Cmsmh.class));
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmh> getCmsmhListByMhac(String mhac, int mham) throws DatabaseException {
		String sql = "SELECT mhaa,mhab,mhac,ma.maab mhac_str,mhad,mhae,mhaf,mhag,mhah,mhai,mhaj,mhak,mhal,mham FROM cmsmh mh LEFT JOIN cmsma ma ON ma.maaa=mh.mhac WHERE mhac=? AND mham=? ";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql, new Object[]{mhac, mham}, new BeanPropertyRowMapper<Cmsmh>(Cmsmh.class));
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

}
